package de.szut.dqi12.sqlitebrowser.model;

import java.io.File;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import de.szut.dqi12.sqlitebrowser.controller.ErrorMessages;
import de.szut.dqi12.sqlitebrowser.controller.GUIController;
//
/**
 * SQLite
 * Model class for SQLite querys etc.
 * @author Alexander
 *
 */

public class SQLite {

	private java.sql.Connection connection;
	private Statement statement;
	private ResultSet resultset;
	private ResultSetMetaData rsmd;
	private ResultSet rowNumber;
	private int columnCount;
	private ArrayList<String> tableNames = new ArrayList<String>();
	private Table actualTable;
	
	/**
	 * Executes a sqlite query
	 * @param query - SQLite query
	 * @return Table object with all informations to update the JTable on the view
	 */
	
	public Table executeQuery(String query) {
		try {
			resultset = statement.executeQuery(query);
			rsmd = resultset.getMetaData();
			columnCount = rsmd.getColumnCount();
			rowNumber = connection.createStatement().executeQuery("Select count(*) from " + getTableName(query));
			actualTable = new Table(getRowNumber(), getTableName(query), getColumnNames(), getContent());
			return actualTable;
		} catch(SQLException e) {
			GUIController.getInstance().executeErrorMessageDialog(ErrorMessages.SQL_QUERY_FAILED + " " + ErrorMessages.SQL_FAILED);
			return null;
		} catch(NullPointerException e) {
			return null;
		}
	}
	
	/**
	 * Adds the limit command to the query
	 * @param query - SQLite query
	 * @param start - first row
	 * @param end - last row
	 * @return Table object with all informations to update the JTable on the view
	 */
	
	public Table executeQuery(String query, int start, int end) {
		String updatedQuery = query.toUpperCase();
		if(!updatedQuery.contains("LIMIT")) {
			if(start < (end - 1) && start >= 0) {
				updatedQuery = updatedQuery + " LIMIT " + start + "," + (end - start);
			} else if(start >= 0) {
				GUIController.getInstance().executeErrorMessageDialog(ErrorMessages.SQL_QUERY_FAILED + " Limit end value is to small! Row number " + (start + 1) + " will be displayed");
				updatedQuery = updatedQuery + " LIMIT " + start + "," + 1;
			}
		}
		return executeQuery(updatedQuery);
	}

	/**
	 * creates a connection to a database
	 * @param path - Path of a .db3 database file
	 * @return false if the connection failed. true if not.
	 */
	
	private boolean createConnection(String path) {
		try {
			this.connection = DriverManager.getConnection("jdbc:sqlite:" + path);
			this.statement = connection.createStatement();
		} catch (SQLException e) {
			GUIController.getInstance().executeErrorMessageDialog(ErrorMessages.SQL_FAILED);
			return false;
		}
		return true;
	}
	
	/**
	 * checks if the connection to a database is successful
	 * @param file - .db3 database file 
	 * @return Arrays with all table names
	 */
	
	public String[] openDatabase(File file) {
		if(createConnection(file.getPath())) {
			return getTableNames();
		} 
		GUIController.getInstance().executeErrorMessageDialog(ErrorMessages.SQL_FAILED);
		return null;
	}
	
	/**
	 * reads the table names from the connection meta data
	 * @return Arrays with all table names
	 */
	
	private String[] getTableNames() {
		try {	
			DatabaseMetaData metaData = connection.getMetaData();
			ResultSet resultSet = metaData.getTables(null, null, "%", null);
			while (resultSet.next()) {
				tableNames.add(resultSet.getString("TABLE_NAME"));
			}
			return tableNames.toArray(new String[tableNames.size()]);
		} catch (SQLException e) {
			GUIController.getInstance().executeErrorMessageDialog(ErrorMessages.SQL_FAILED);
			return null;
		}
	}
	
	/**
	 * reads the table name out of a query
	 * @param query - sqlite query
	 * @return table name as a String
	 */
	
	private String getTableName(String query) {
		String[] splittedQuery = query.toLowerCase().split(" ");
		String tableName = "";
		int start = 0;
        int end = 0;
        //While loop, which is searching the from in the query"
        while(!splittedQuery[start].contains("from")) {
                start++;
                end++;
        }
        //Checks if the table name could be longer than one word and counts the words
        if(splittedQuery[start +1].startsWith("'") || splittedQuery[start +1].startsWith(String.valueOf('"'))) {
                while(!splittedQuery[end +1].endsWith("'") && !splittedQuery[end +1].endsWith(String.valueOf('"'))) {
                	end++;
                }
        }
        //Iterates throught the splittedQuery to get the whole table name
        for(int i = start; i <= end; i++) {
                tableName = tableName + " " + splittedQuery[i + 1];     
        }
        return tableName;
	}
	
	/**
	 * Gets all column names of one table
	 * @return All column names of one table
	 */
	
	private String[] getColumnNames() {
		try {
			String[] columnNames = new String[columnCount];
			for(int i = 1; i <= columnCount; i++) {
				columnNames[i - 1] = rsmd.getColumnName(i);
			}
			return columnNames;
		} catch(SQLException e) {
			GUIController.getInstance().executeErrorMessageDialog(ErrorMessages.SQL_FAILED);
			return null;
		}
	}
	
	/**
	 * Reads the content of a resultset
	 * @return The results as 2D String array (table)
	 */
	
	private String[][] getContent() {
		try {
			ArrayList<String[]> content = new ArrayList<String[]>();
			while(resultset.next()) {
				content.add(new String[columnCount]);
                for(int i = 0; i < columnCount; i++) {
                	content.get(content.size() - 1)[i] = resultset.getString(i+1);
                }
			}
			return (String[][]) content.toArray(new String[content.size()][]);
		} catch (SQLException e) {
			GUIController.getInstance().executeErrorMessageDialog(ErrorMessages.SQL_FAILED);
			return null;
		}
	}
	
	/**
	 * gets the ammount of rows of the table
	 * @return the ammount of rows (0 if sth. went wrong)
	 */
	
	private int getRowNumber() {
		try {
			return rowNumber.getInt(1);
		} catch (SQLException e) {
			GUIController.getInstance().executeErrorMessageDialog(ErrorMessages.SQL_FAILED);
			return 0;
		}
	}
}
